package com.citycloud.ccuap.tc.admin.datagroup;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.SelectProvider;
import org.springframework.cache.annotation.Cacheable;

import com.citycloud.ccuap.commons.db.SqlRestrictions;
import com.citycloud.ccuap.commons.sys.entity.SysRole;
import com.citycloud.ccuap.tc.admin.entity.SysDatagroup;

@Mapper
public interface DataGroupMapper {
	@SelectProvider(type = SqlProvider.class, method = "findSysRoleByDatagroupId")
	@ResultMap("com.citycloud.ccuap.commons.sys.entity.SysRole.ResultMapExt")
	public List<SysRole> findSysRoleByDatagroupId(String datagroupId);

	@SelectProvider(type = SqlProvider.class, method = "getAllList")
	@ResultMap("com.citycloud.ccuap.tc.admin.entity.SysDatagroup.ResultMapExt")
	public List<SysDatagroup> getAllList(String userId, String projectId);
	
	@SelectProvider(type = SqlProvider.class, method = "getAllListNew")
	public List<Map<String,Object>> getAllListNew(String userId, String projectId);

	@SelectProvider(type = SqlProvider.class, method = "getAllListWithOutProjectId")
	@ResultMap("com.citycloud.ccuap.tc.admin.entity.SysDatagroup.ResultMapExt")
	@Cacheable(cacheNames="allListCache", key = "#p0 + '_' + #p1")
	public List<SysDatagroup> getAllListWithOutProjectId(String userId, String projectId);
	
	@SelectProvider(type = SqlProvider.class, method = "getAllListWithOutProjectIdNew")
	public List<Map<String,Object>> getAllListWithOutProjectIdNew(String userId, String projectId);

	@SelectProvider(type = SqlProvider.class, method = "getDataGroupByBustypeId")
	@ResultMap("com.citycloud.ccuap.tc.admin.entity.SysDatagroup.ResultMapExt")
	public SysDatagroup getDataGroupByBustypeId(String objId);
	
	@SelectProvider(type = SqlProvider.class, method = "findDataGroupByRoleId")
	@ResultMap("com.citycloud.ccuap.tc.admin.entity.SysDatagroup.ResultMapExt")
	public List<SysDatagroup> findDataGroupByRoleId(Long roleId, Long projectId);
	
	@SelectProvider(type = SqlProvider.class, method = "findLevelByDatagroupId")
	public List<Map<String,Object>> findLevelByDatagroupId(String groupId);
	
	@SelectProvider(type = SqlProvider.class, method = "findDatagroupIdsByRoleId")
	@ResultType(value=String.class)
	public List<String> findDatagroupIdsByRoleId(Long roleId);


	@SelectProvider(type = SqlProvider.class, method = "getByRoleId")
	@ResultMap("com.citycloud.ccuap.tc.admin.entity.SysDatagroup.ResultMapExt")
	public List<SysDatagroup> getByRoleId(Long roleId);

	class SqlProvider {

		public String getByRoleId(Long roleId) {
			String sql = "SELECT t1.* FROM sys_datagroup t1  left join sys_project_dg_role_rel t2 on t1.datagroup_id = t2.datagroup_id where t1.is_enable = 1";
			sql += SqlRestrictions.eq("t2.role_id", roleId);
			return sql;
		}

		public String getDataGroupByBustypeId(String objId) {
			String sql = "SELECT * FROM sys_datagroup AS sd LEFT JOIN sys_dimension_bustype AS ats_db ON ats_db.DATAGROUP_ID = sd.datagroup_id WHERE 1 = 1";
			sql += SqlRestrictions.eq("ats_db.OBJ_ID", objId);
			return sql;
		}
		
		public String findSysRoleByDatagroupId(String datagroupId) {
			String sql = "SELECT t1.* FROM sys_role t1,sys_project_dg_role_rel t2 where t1.role_id=t2.role_id";
			sql += SqlRestrictions.eq("t2.datagroup_id", datagroupId);
			return sql;
		}
		
		public String findDatagroupIdsByRoleId(Long roleId){
			StringBuffer sql = new StringBuffer( "select distinct t1.datagroup_id from sys_project_dg_role_rel t1 inner join sys_datagroup t2 on t1.datagroup_id = t2.datagroup_id where t2.is_enable = 1 and t2.is_deleted = 0 ");
			sql.append(SqlRestrictions.eq("t1.role_id",roleId));
			return sql.toString();
		}

		public String getAllList(String userId, String projectId) {
			StringBuffer sql = new StringBuffer(
					"SELECT distinct sd.* FROM sys_role AS sr , sys_act AS sa ,sys_project_dg_role_rel AS sad ,sys_datagroup sd " +
							"WHERE sa.role_id = sr.role_id and sr.role_id = sad.role_id AND sad.datagroup_id = sd.datagroup_id ");
			sql.append(SqlRestrictions.eq("sd.PROJECT_ID", projectId));
			sql.append(SqlRestrictions.eq("sr.PROJECT_ID", projectId));
			sql.append(SqlRestrictions.eq("sa.user_id", userId));
			return sql.toString();
		}

		//getAllList不传租户ID
		public String getAllListWithOutProjectId(String userId, String projectId) {
			StringBuffer sql = new StringBuffer(
					"SELECT distinct sd.* FROM sys_role AS sr , sys_act AS sa ,sys_project_dg_role_rel AS sad ,sys_datagroup sd " +
							"WHERE sa.role_id = sr.role_id and sr.role_id = sad.role_id AND sad.datagroup_id = sd.datagroup_id and sd.is_enable=1");
//			sql.append(SqlRestrictions.eq("sd.PROJECT_ID", projectId));
			sql.append(SqlRestrictions.eq("sr.PROJECT_ID", projectId));
			sql.append(SqlRestrictions.eq("sa.user_id", userId));
			return sql.toString();
		}

		public String findDataGroupByRoleId(Long roleId,Long projectId) {
			String sql = "SELECT t1.* FROM sys_datagroup t1 inner join sys_project_dg_rel s on t1.datagroup_id=s.datagroup_id and s.project_id= "+projectId+" left join sys_project_dg_role_rel t2 on t1.datagroup_id = t2.datagroup_id where t1.is_enable = 1";
			sql += SqlRestrictions.eq("t2.role_id", roleId);
			return sql;
		}
		
		//getAllList不传租户ID
		public String getAllListWithOutProjectIdNew(String userId, String projectId) {
			StringBuffer sql = new StringBuffer(
					"SELECT distinct sdl.datagroup_id groupId, sc.code_value codeValue FROM sys_datagroup_level sdl, sys_code sc WHERE sdl.level_id = sc.code_id AND "
					+ "sdl.datagroup_id IN ( SELECT DISTINCT sd.OBJ_ID FROM sys_role AS sr, sys_act AS sa, "
					+ "sys_project_dg_role_rel AS sad, sys_datagroup sd WHERE sa.role_id = sr.role_id AND "
					+ "sr.role_id = sad.role_id AND sad.datagroup_id = sd.datagroup_id AND sd.project_id = sr.project_id ");
			sql.append(SqlRestrictions.eq("sr.PROJECT_ID", projectId));
			sql.append(SqlRestrictions.eq("sa.user_id", userId));
			sql.append(")");
			return sql.toString();
		}
		
		public String getAllListNew(String userId, String projectId) {
			StringBuffer sql = new StringBuffer(
					"SELECT distinct sdl.datagroup_id groupId, sc.code_value codeValue FROM sys_datagroup_level sdl, sys_code sc WHERE sdl.level_id = sc.code_id AND "
					+ "sdl.datagroup_id IN ( SELECT DISTINCT sd.OBJ_ID FROM sys_role AS sr, sys_act AS sa, "
					+ "sys_project_dg_role_rel AS sad, sys_datagroup sd WHERE sa.role_id = sr.role_id AND "
					+ "sr.role_id = sad.role_id AND sad.datagroup_id = sd.datagroup_id AND sd.project_id = sr.project_id ");
			sql.append(SqlRestrictions.eq("sr.project_id", projectId));
			sql.append(SqlRestrictions.eq("sa.user_id", userId));
			sql.append(")");
			return sql.toString();
		}
		
		public String findLevelByDatagroupId(String groupId) {
			String sql = "SELECT sc.code_value codeValue FROM sys_datagroup_level sd  inner join sys_code sc ON sd.level_id = sc.code_id WHERE 1 = 1";
			sql += SqlRestrictions.eq("sd.datagroup_id", groupId);
			return sql;
		}

	}

	

	
	
	
}
